﻿CREATE PROCEDURE BILL.GetBillingHistory 
	@start DATETIME = NULL,
	@end DATETIME = NULL,
	@customer VARCHAR(50) = NULL,
	@payment VARCHAR(10) = NULL
AS
BEGIN
	-----------------------
	-- Common procedures variables
	-----------------------
	DECLARE 
		--Start of the message to be printed at the end of the procedure.
		@finishEndMSG VARCHAR(300),
		--The name of the procedure with its schema.
		@procName VARCHAR(300),
		--The result of the procedure. Ifall goes ok it will be 0, else it will contain the code
		--of the error that has been thrown.
		@result INT,
		-- The result message of the procedure. If all goes ok it will be empty, else it will contain the message
		-- of the error that has been thrown.
		@resultMessage NVARCHAR(100)


	SELECT
		@procName = OBJECT_SCHEMA_NAME(@@PROCID) + '.' + OBJECT_NAME(@@PROCID),
		@result = 0
	
	SET @finishEndMSG =  @procName + ' finished.'


	-----------------------
	-- Parameters default
	-----------------------
	IF (@end IS NULL)
		SET @end = GETDATE()

	IF (@start IS NULL)
		SET @start = DATEADD(DAY, -7, @end)


	SET @customer = LTRIM(RTRIM(@customer))


	-----------------------
	-- Variables
	-----------------------
	DECLARE @id INT

	DECLARE @billProductsT TABLE (BillId INT, Price FLOAT)


	-----------------------
	-- Begin Query
	-----------------------
	IF (@result = 0)
	BEGIN
		BEGIN TRY
			INSERT INTO @billProductsT
			SELECT 
				billItem.BillId, 
				Price = ROUND(SUM(billItem.Price * billItem.Count), 2)
			FROM BILL.BillItems billItem
				JOIN BILL.Bills bill ON bill.Id = billItem.BillId
				--JOIN CAT.Products product ON product.Id = billItem.ProductId
			WHERE
				bill.Datetime BETWEEN @start AND @end
			GROUP BY
				billItem.BillId

			SELECT
				[bill].[Id],
				[bill].[CustomerId],
				[Datetime] = CONVERT(VARCHAR, [bill].[Datetime], 121), 
				[Pay] = ISNULL([bill].[Pay], 0), 
				[bill].[Sealed], 
				[Timestamp] = CONVERT(VARCHAR, [bill].[Timestamp], 121),
				Customer = customer.Code + ' ' + customer.Name,
				Price = billProducts.Price
			FROM BILL.Bills bill
				JOIN @billProductsT billProducts ON billProducts.BillId = bill.Id
				LEFT JOIN CAT.Customers customer ON customer.Id = bill.CustomerId
			WHERE
				(
					@customer IS NULL
					OR
					customer.Name LIKE '%' + @customer + '%'
					OR
					CAST(customer.Code AS VARCHAR) LIKE '%' + @customer + '%'
				)
				AND
				(
					@payment IS NULL
					OR
					(
						@payment = 'pending'
						AND
						billProducts.Price > ROUND(bill.Pay, 2)
					)
					OR
					(
						@payment = 'paid'
						AND
						billProducts.Price <= ROUND(bill.Pay, 2)
					)
				)
			ORDER By
				bill.Datetime DESC

		END TRY
		BEGIN CATCH
			SELECT
				@result = ERROR_NUMBER(),
				@resultMessage = ERROR_MESSAGE()
		END CATCH
	END


	-----------------------
	-- Finish procedure
	-----------------------
	IF (@result = 0)
		PRINT @finishEndMSG
	ELSE 
		PRINT @finishEndMSG + ' Result: ' + CAST(@result AS VARCHAR) + ', message: ' + @resultMessage

	RETURN @result
END

GO
GRANT EXECUTE
    ON OBJECT::BILL.GetBillingHistory TO [SystemPublic]
    AS [dbo];